package com.dailyblue.java.servlet.jsp.dao;

import com.dailyblue.java.servlet.jsp.bean.Emp;
import com.dailyblue.java.servlet.jsp.db.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDAO {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    public List<Emp> findAllEmp() {
        List<Emp> list = new ArrayList<>();
        String sql = "select * from emp where state=1";
        try {
            conn = DBManager.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int empNo = rs.getInt(1);
                String ename = rs.getString(2);
                String job = rs.getString("job");
                Integer mgr = rs.getInt("mgr");
                String hireDate = rs.getString("hireDate");
                Double sal = rs.getDouble(6);
                Double comm = rs.getDouble(7);
                Integer deptNo = rs.getInt(8);
                Emp emp = new Emp(empNo, ename, job, mgr, hireDate, sal, comm, deptNo, 1);
                list.add(emp);
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // DBManager.closeConnection(conn, ps, rs);
        }

        return list;
    }

    public void saveEmp(Emp emp) {
        String sql = "insert into emp values(null,?,?,?,now(),?,?,?,1)";
        try {
            conn = DBManager.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, emp.getEname());
            ps.setString(2, emp.getJob());
            ps.setInt(3, emp.getMgr());
            ps.setDouble(4, emp.getSal());
            ps.setDouble(5, emp.getComm());
            ps.setInt(6, emp.getDeptNo());
            ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBManager.closeConnection(conn, ps, null);
        }
    }

    public List<String> findAllJob(){
        List<String> list = new ArrayList<>();
        String sql = "select distinct job from emp where state=1";
        try {
            conn = DBManager.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                list.add(rs.getString(1));
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }
}
